﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: August 11, 2015
-- Description:	This function takes the relationship name and the desired permission 
--				and returns the actual permission.  If the current relationship has 
--				null for the requested permission, it checks up the chain of 
--				superrelationships until it finds one without null in the specified 
--				permission.
-- =============================================
CREATE FUNCTION [dbo].[SuperRelationshipPermission] 
(
	-- Add the parameters for the function here
	@RelationshipName nvarchar(255),
	@IsRevRelationship bit, 
	@RequestedPermission nvarchar(20)
)
RETURNS int
AS
BEGIN
	DECLARE @EffectivePermission int
	DECLARE @SuperRelationship nvarchar(255) 
	DECLARE @1RD2 int, @1RC2 int,@1WD2 int, @1WC2 int
	DECLARE @2RD1 int, @2RC1 int,@2WD1 int, @2WC1 int
	-- If the RelationshipName is null, this is the end of a super-relationship chain - return 0
	IF @RelationshipName IS NULL
		Return 0
	---- Populate the values from the DefinedEntityRelationships table (the abbreviations should be obvious here :)
	SELECT @SuperRelationship = SuperRelationship,@1RD2 = E1_Read_Direct_E2,@1RC2 = E1_Read_Child_E2,
		@1WD2 = E1_Write_Direct_E2,@1WC2 = E1_Write_Child_E2, @2RD1 = E2_Read_Direct_E1, @2RC1 = E2_Read_Child_E1,
		@2WD1 = E2_Write_Direct_E1,@2WC1 = E2_Write_Child_E1
		
	FROM DefinedEntityRelationships
	WHERE ShortRelationshipName = @RelationshipName

	-- If the requested permission is not from a reverse relationship, use this set of if statements.
	IF @IsRevRelationship = 0
	BEGIN
		-- If the permission is Request, the appropriate access will be stored in E1_Read_Direct_E2
		-- if the permission is null, move one level up the chain and try again.
		IF @RequestedPermission = 'ReadDirect'
		BEGIN
			IF NOT(@1RD2 IS NULL)
				Return @1RD2
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
		ELSE IF @RequestedPermission = 'ReadChildren'
		BEGIN
			IF NOT(@1RC2 IS NULL)
				Return @1RC2
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
		ELSE IF @RequestedPermission = 'WriteDirect'
		BEGIN
			IF NOT(@1WD2 IS NULL)
				Return @1WD2
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
		ELSE IF @RequestedPermission = 'WriteChildren'
		BEGIN
			IF NOT(@1WC2 IS NULL)
				Return @1WC2
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
	END
	-- If the requested permission is from a reverse relationship, use this set of if statements.
	ELSE
	BEGIN
		IF @RequestedPermission = 'ReadDirect'
		BEGIN
			IF NOT(@2RD1 IS NULL)
				Return @2RD1
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
		ELSE IF @RequestedPermission = 'ReadChildren'
		BEGIN
			IF NOT(@2RC1 IS NULL)
				Return @2RC1
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
		ELSE IF @RequestedPermission = 'WriteDirect'
		BEGIN
			IF NOT(@2WD1 IS NULL)
				Return @2WD1
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
		ELSE IF @RequestedPermission = 'WriteChildren'
		BEGIN
			IF NOT(@2WC1 IS NULL)
				Return @2WC1
			ELSE
				Return dbo.SuperRelationshipPermission(@SuperRelationship,@IsRevRelationship,@RequestedPermission)
		END
	END

	RETURN 0

END